package com.A1908.dao.impl;

import com.A1908.entity.*;
import com.A1908.entity.LineDate;
import javafx.scene.shape.LineTo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DataAnalysisDaoImpl extends BaseDao{
    Connection connection=null;
    ResultSet rs=null;
    PreparedStatement pstmt=null;
    //获得树状图的数据
    public List<TreeDataAnalysis> getTreeDetailFeastInfo(User user){
        String sql="SELECT\n" +
                "\tCOUNT(*),\n" +
                "\tfeastType,\n" +
                "\tuserfeasttype.typeName\n" +
                "FROM\n" +
                "\tfeastinfo \n" +
                "INNER JOIN userfeasttype ON feastinfo.feastType=userfeasttype.id\n" +
                "WHERE\n" +
                "\tfeastUser = ?\n" +
                "GROUP BY\n" +
                "\tfeastinfo.feastType;";
        Object[] objects={user.getId()};
        connection = conn();
        pstmt=super.pstmt(connection,sql,objects);
        try {
            rs=pstmt.executeQuery();
            List<TreeDataAnalysis> info=new ArrayList<>();
            while (rs.next()){
                TreeDataAnalysis d=new TreeDataAnalysis();
                d.setTreeCountArr(rs.getInt(1));
                d.setTreeTypeArr(rs.getString(3));
                info.add(d);
            }
            return info;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            cloesAll(connection,pstmt,rs);
        }
        return null;
    }
    //获得饼状图的数据
    public List<PieDataAnalysis> getPieDetailInfo(User user){
     String sql="SELECT\t\n" +
             "\tSUM( costMoney ),\n" +
             "\trelationtype.relationType \n" +
             "FROM\n" +
             "\tmyattend\n" +
             "\tINNER JOIN relationtype ON myattend.hostRealtion = relationtype.id \n" +
             "WHERE myUserID=?\n" +
             "GROUP BY\n" +
             "\trelationtype.id";
     Object [] objects={user.getId()};
     connection=conn();
     pstmt=super.pstmt(connection,sql,objects);
        try {
            rs=pstmt.executeQuery();
            List<PieDataAnalysis> list=new ArrayList<>();
            while (rs.next()){
                PieDataAnalysis pie=new PieDataAnalysis();
                pie.setPieCountArr(rs.getInt(1));
                pie.setPieTypeArr(rs.getString(2));
                list.add(pie);
            }
            return  list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            cloesAll(connection,pstmt,rs);
        }
        return null;
    }

    //获得折线图时间
    public List<LineDate> getlineDataAnalyses(User user){
        String sql="SELECT\n" +
                "\tattendpeopleinfo.attendDate \n" +
                "FROM\n" +
                "\tattendpeopleinfo \n" +
                "\tINNER JOIN feastinfo ON feastinfo.id =attendpeopleinfo.attendFeast\n" +
                "\tWHERE feastinfo.feastUser=?\n" +
                "GROUP BY\n" +
                "\tattendpeopleinfo.attendDate;";
        Object[]objs={user.getId()};
        connection=conn();
        pstmt=super.pstmt(connection,sql,objs);
        try {
            rs=pstmt.executeQuery();
            List<LineDate> list=new ArrayList<>();
            while (rs.next()){
                LineDate date=new LineDate();
                    date.setAttendData(rs.getString(1));
                    list.add(date);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            cloesAll(connection,pstmt,rs);
        }
        return null;
    }

    //获得折线图人物关系
    public List<LineRelationData> getLineRelationData(User user){
        String sql="SELECT\n" +
                "\trelationID,\n" +
                "\trelationtype.relationType\n" +
                "FROM\n" +
                "\tattendpeopleinfo \n" +
                "\tINNER JOIN feastinfo ON feastinfo.id =attendpeopleinfo.attendFeast\n" +
                "\tINNER JOIN relationtype ON relationtype.id = attendpeopleinfo.relationID \t\n" +
                "\tWHERE feastinfo.feastUser=?\n" +
                "GROUP BY\n" +
                "\tattendpeopleinfo.relationID;";
        Object[]objects={user.getId()};
        connection=conn();
        pstmt=super.pstmt(connection,sql,objects);
        try {
            rs=pstmt.executeQuery();
            List<LineRelationData> list=new ArrayList<>();
            while (rs.next()){
                LineRelationData relationData=new LineRelationData();
                relationData.setRelationID(rs.getInt(1));
                relationData.setRelationTypeName(rs.getString(2));
                list.add(relationData);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            cloesAll(connection,pstmt,rs);
        }
        return null;
    }

    //获得折线图数据
    public List<LineTotalData> getLineTotal(User user){
        String sql="SELECT\t\n" +
                "\tsum( attendpeopleinfo.gift ),\t\n" +
                "\tattendpeopleinfo.relationID,\n" +
                "\tattendpeopleinfo.attendDate \n" +
                "FROM\n" +
                "\tattendpeopleinfo\n" +
                "\tINNER JOIN relationtype ON relationtype.id = attendpeopleinfo.relationID \t\n" +
                "\tINNER JOIN feastinfo ON feastinfo.id =attendpeopleinfo.attendFeast\n" +
                "\tWHERE feastinfo.feastUser=?\n" +
                "GROUP BY\n" +
                "\tattendpeopleinfo.attendDate,\n" +
                "\tattendpeopleinfo.relationID\n" +
                "ORDER BY\n" +
                "\tattendpeopleinfo.attendDate ASC;\n" +
                "\t";
        Object [] objects={user.getId()};
        connection=conn();
        pstmt=super.pstmt(connection,sql,objects);
        try {
            rs=pstmt.executeQuery();
            List<LineTotalData> totalDataList=new ArrayList<>();
            while (rs.next()){
                LineTotalData totalData=new LineTotalData();
                totalData.setLineCount(rs.getInt(1));
                totalData.setRelationID(rs.getInt(2));
                totalData.setAttendDate(rs.getString(3));
                totalDataList.add(totalData);
            }
            return  totalDataList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            cloesAll(connection,pstmt,rs);
        }

        return null;
    }
}
